﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class CampusDAL
    {/// <summary>
     /// 列表绑定
     /// </summary>
     /// <param name="camName">列表名称</param>
     /// <param name="camName">开始位置</param>
     /// <param name="camName">结束为止</param>
     /// /// <param name="total">满足条件的总记录数，out输出参数</param>
     /// <returns></returns>
        public List<CampusInfo> GetCampus(string camName, int begin, int end, out int total)
        {
            //如果校区为空，查询所有数据
            //如果校区不为空，根据校区名称模糊查询
            string sql = "";
            string strWhere = " where 1=1";
            string abc = string.Format(" where 1=1 and CampusName like '%{0}%'", camName);

            if (camName == string.Empty)
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by CampusID desc)
            rowindex,* from CampusInfo {2}) a where rowindex between {0} and {1}", begin, end, strWhere);
            }
            else
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by CampusID)
            rowindex,* from CampusInfo) a {0}", abc);
            }
            DataTable dt = DBHelper.GetTable(sql);
            List<CampusInfo> list = new List<CampusInfo>();
            //将datatable转换成list
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    CampusInfo info = new CampusInfo();//实例化放在for里面
                    info.CampusID = Convert.ToInt32(dt.Rows[i]["CampusID"]);
                    info.CampusName = dt.Rows[i]["CampusName"].ToString();
                    info.CampusReMark = dt.Rows[i]["CampusReMark"].ToString();
                    list.Add(info);
                }
            }
            string sqltotal = " select COUNT(*) from CampusInfo" + strWhere;
            DataTable totalDt = DBHelper.GetTable(sqltotal);
            total = Convert.ToInt32(totalDt.Rows[0][0]);
            return list;
        }


        public string Add(CampusInfo campus)
        {
            string sql = string.Format(" insert into CampusInfo(CampusName,CampusReMark) values('{0}','{1}')", campus.CampusName, campus.CampusReMark);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "Error";
        }

        public string Update(CampusInfo campus)
        {
            string sql = string.Format("update CampusInfo set CampusName = '{0}',CampusReMark= '{1}'  where CampusID ={2}", campus.CampusName, campus.CampusReMark, campus.CampusID);
            int i = DBHelper.ExecutNonQuery(sql);

            return i > 0 ? "OK" : "Error";
        }

        public string Delete(int cid) 
        {
            string sql = string.Format("delete from CampusInfo where CampusID={0}", cid);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "Error";
        }

    }
}
